package cn.easybuy.dao.product;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.easybuy.dao.user.BaseDaoImpl;
import cn.easybuy.entity.Product;
import cn.easybuy.param.ProductParam;
import cn.easybuy.utils.EmptyUtils;

public class ProductDaoImpl extends BaseDaoImpl implements ProductDao {

	public ProductDaoImpl(Connection connection) {
		super(connection);
	}

	@Override
	public List<Product> queryProductList(ProductParam params) throws SQLException {
		
		List<Product> pList = new ArrayList<Product>();
		StringBuffer sql = new StringBuffer("SELECT * FROM easybuy_product WHERE 1 = 1");
		List<Object> paramsList = new ArrayList<Object>();
		ResultSet rs = null;
		
		if (EmptyUtils.isNotEmpty(params.getKeyword())) {
			sql.append(" and name like ?" );
			paramsList.add("%"+params.getKeyword() + "%");
		}
		if (EmptyUtils.isNotEmpty(params.getCategoryId())) {
			sql.append(" AND categoryLevel1Id = ? OR categoryLevel2Id = ? OR categoryLevel3Id = ?");
			paramsList.add(params.getCategoryId());
			paramsList.add(params.getCategoryId());
			paramsList.add(params.getCategoryId());
		}
		if (EmptyUtils.isNotEmpty(params.getSort())) {
			sql.append(" ORDER BY " + params.getSort());
		}
		if (EmptyUtils.isNotEmpty(params.isPage())) {
			sql.append(" LIMIT " + params.getStartIndex() + "," + params.getPageSize());
		}
		rs = this.executeQuery(sql.toString(), paramsList.toArray());
		
		try {
			while (rs.next()) {
				Product p = tableToClass(rs);
				pList.add(p);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			this.closeResource(rs);
			this.closeResource();
		}
		return pList;
	}

	@Override
	public int queryProductCount(ProductParam params) {

		Integer num = 0;
		List<Product> pList = new ArrayList<Product>();
		StringBuffer sql = new StringBuffer("SELECT COUNT(*) count FROM easybuy_product WHERE 1 = 1");
		List<Object> paramsList = new ArrayList<Object>();
		ResultSet rs = null;
		
		if (EmptyUtils.isNotEmpty(params.getKeyword())) {
			sql.append(" and name like ?" );
			paramsList.add("%"+params.getKeyword() + "%");
		}
		if (EmptyUtils.isNotEmpty(params.getCategoryId())) {
			sql.append(" AND categoryLevel1Id = ? OR categoryLevel2Id = ? OR categoryLevel3Id = ?");
			paramsList.add(params.getCategoryId());
			paramsList.add(params.getCategoryId());
			paramsList.add(params.getCategoryId());
		}
		
		rs = this.executeQuery(sql.toString(), paramsList.toArray());
		
		try {
			while (rs.next()) {
				num = rs.getInt("count");
			}
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			this.closeResource(rs);
			this.closeResource();
		}
		return num;
	}

	@Override
	public Product tableToClass(ResultSet rs) throws Exception {
		Product product = new Product();
        product.setId(rs.getInt("id"));
        product.setName(rs.getString("name"));
        product.setDescription(rs.getString("description"));
        product.setPrice(rs.getFloat("price"));
        product.setStock(rs.getInt("stock"));
        product.setCategoryLevel1Id(rs.getInt("categoryLevel1Id"));
        product.setCategoryLevel2Id(rs.getInt("categoryLevel2Id"));
        product.setCategoryLevel3Id(rs.getInt("categoryLevel3Id"));
        product.setFileName(rs.getString("fileName"));
        return product;
	}
	
	@Override
	public Product getProductById(Integer id) throws SQLException {
		String sql = " select id,name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete from easybuy_product where id = ? ";
		ResultSet resultSet = null;
		Product product = null;
		try {
			Object params[] = new Object[] { id };
			resultSet = this.executeQuery(sql, params);
			while (resultSet.next()) {
				product = tableToClass(resultSet);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closeResource(resultSet);
			this.closeResource();
			return product;
		}
	}

}
